<?php

require_once __DIR__.'/../../vendor/autoload.php';

$app = new Silex\Application();

$app->register(new Silex\Provider\DoctrineServiceProvider(), array(
    'db.options' => array (
        'driver'    => 'pdo_mysql',
        'host'      => 'hackathon.clizlzc6z8gr.us-east-1.rds.amazonaws.com',
        'dbname'    => 'hackathon',
        'user'      => 'hackathon',
        'password'  => 'symp2013',
        'charset'   => 'utf8',
    )
));


$app->before(function () use ($app) {
    if (0 === strpos($app['request']->headers->get('Content-Type'), 'application/json')) {
        $data = json_decode($app['request']->getContent(), true);
        $app['request']->request->replace(is_array($data) ? $data : array());
    }
});

//list
$app->get('/providers', function () use ($app) {
    $where='';
    
    if($searchText = $_GET['searchText']) {
        $where = "WHERE Provider.provname like '%$searchText%'";
    }

	if (isset($_GET['searchZipCode']) && isset($_GET['searchDistance'])) {
		$sql = "select latitude, longitude from zip_code where zip = '{$_GET['searchZipCode']}'";
		$location = $app['db']->fetchAll($sql);

		//harcoding distance
		$_GET['searchDistance'] = 20;

		if ($location) {
			$sql = "SELECT zip, SQRT(POW(69.1 * (latitude - {$location[0]['latitude']}), 2) + POW(69.1 * ({$location[0]['longitude']} - longitude) * COS(latitude / 57.3), 2)) AS distance FROM zip_code HAVING distance < {$_GET['searchDistance']} ORDER BY distance";
			$zips = $app['db']->fetchAll($sql);
			if (count($zips)) {				
				if ($where) {
					$where .= ' and ';
				} else {
					$where .= ' where ';
				}
				$where .= ' zip in (';
				foreach ($zips as $code) {
					$where .= "'{$code['zip']}',";
				}
				$where = trim($where,',') . ')';
			}
		}
	}

    if($sortInfo = $_GET['sortInfo']) {
        $orderby = "ORDER BY $sortInfo";
    }
    $orderby = "ORDER BY score DESC";
    $pageSize = $_GET['pageSize'];
    $pageNumber = $_GET['pageNumber'];

    if($pageSize && $pageNumber) {
        $limit = "LIMIT $pageSize";
        $offset = "OFFSET ".$pageSize * ($pageNumber - 1);
    }

    $sql = "SELECT * from Provider
            $where
            $orderby
            $limit
            $offset
            ";
    $providers = $app['db']->fetchAll($sql);
 $resp = array('totalItems'=>1000,
                  'items'=>$providers);
    return $app->json($resp);
});


//get one by id
$app->get('/providers/{id}', function($id) use ($app){
    $sql = "SELECT * FROM Provider WHERE provnum = ?";
    $provider = $app['db']->fetchAssoc($sql, array($id));

	$sql = "SELECT q1_measure_score as q1score, q1_quarter, q2_measure_score as q2score, q2_quarter, q3_measure_score as q3score, q3_quarter, measure_score_3qtr_avg avgscore FROM quality WHERE provnum = ?";
	$quality = $app['db']->fetchAssoc($sql, array($id));

		$sql = "SELECT pnlty_date, pnlty_type, fine_amt, payden_strt_dt, payden_days FROM penalties WHERE provnum = ?";
		$fines = $app['db']->fetchAll($sql, array($id));		

		$provider = array_merge($provider, $quality);
		$provider['penalties'] = $fines;


		$sql = "SELECT * FROM location WHERE `Federal Provider Number` = '$id'";	
		$provider['location'] = $app['db']->fetchAssoc($sql);			
		
    return $app->json($provider);
});

//list
$app->get('/issues', function () use ($app) {
    $where='';


    if($searchText = $_GET['searchText']) {
        $where = "WHERE issue.issue_title like '%$searchText%'";
    }

    if($sortInfo = $_GET['sortInfo']) {
        $orderby = "ORDER BY $sortInfo";
    }

    $pageSize = $_GET['pageSize'];
    $pageNumber = $_GET['pageNumber'];

    if($pageSize && $pageNumber) {
        $limit = "LIMIT $pageSize";
        $offset = "OFFSET ".$pageSize * ($pageNumber - 1);
    }

    $sql = "SELECT DISTINCT SQL_CALC_FOUND_ROWS issue.issue_id, issue.visible_id, issue.issue_title, project.title AS project_title, contact.fullname AS assignee, picklist_bug_priority.value AS priority, picklist_bug_status.value as status, DATE_FORMAT(issue.modified,'%b %d %Y %h:%i %p') as modified
            FROM issue
            INNER JOIN project ON issue.project=project.project_id
            INNER JOIN contact ON issue.assigned_to=contact.contact_id
            INNER JOIN picklist_bug_status ON issue.status=picklist_bug_status.id
            INNER JOIN picklist_bug_priority ON issue.priority=picklist_bug_priority.id
            $where
            $orderby
            $limit
            $offset
            ";

    $issues = $app['db']->fetchAll($sql);
    $resp = array('totalItems'=>209,
                  'items'=>$issues);
    return $app->json($resp);
});

//add new
$app->post('/issues', function() use ($app) {
    $issue = $app['request']->request->all();
    $issue['issue_id']=md5(time());
    $issue['status']='0080';
    $issue['modified']=date('Y-m-d H:i:s',time());

    $app['db']->insert('issue',$issue);
    return $app->json($issue, 201);
});

//get one by id
$app->get('/issues/{id}', function($id) use ($app){
    $sql = "SELECT issue_id, visible_id, project, reporter, assigned_to, issue_title, summary, priority FROM issue WHERE issue_id = ?";
    $issue = $app['db']->fetchAssoc($sql, array($id));
    return $app->json($issue);
});

//update one
$app->put('/issues/{id}', function($id) use ($app){
    $issue = $app['request']->request->all();
    $app['db']->update('issue', $issue, array('issue_id' => $id));
    return $app->json($issue);
});

//delete one
$app->delete('/issues/{id}', function($id) use ($app){
    $app['db']->delete('issue', array('issue_id' => $id));
    return $app->json($id);
});

//get picklist values
$app->get('/picklist/{table}/{id_field}/{value_field}/{where}', function($table, $id_field, $value_field, $where='') use ($app){
    if($where) {
        $where = "AND ".$where;
    }

    $sql = "SELECT $id_field, $value_field FROM $table WHERE $value_field !='' $where ORDER BY $value_field LIMIT 200";
    $picklist = $app['db']->fetchAll($sql);
    return $app->json($picklist);
});

$app->run();
